package ch.uzh.ifi.sonar.storage;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.List;
import java.util.Properties;

import org.eclipse.emf.ecore.EObject;
import org.eclipse.emf.ecore.EPackage;

import org.eclipse.emf.teneo.hibernate.HbDataStore;
import org.eclipse.emf.teneo.hibernate.HbHelper;
import org.eclipse.jface.dialogs.MessageDialog;
import org.eclipse.jface.preference.IPreferenceStore;
import org.eclipse.swt.SWT;
import org.eclipse.ui.PlatformUI;
import org.eclipse.emf.teneo.PersistenceOptions;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.MySQL5Dialect;

import ch.uzh.ifi.sonar.Activator;
import ch.uzh.ifi.sonar.model.ModelPackage;

public class TeneoHibernateStorage implements IStorage {
	private String storageIdentifier;
	private HbDataStore dataStore = null;
	private final IPreferenceStore preferenceStore;

	public TeneoHibernateStorage(String storageIdentifier) {
		preferenceStore = Activator.getDefault().getPreferenceStore();
		this.init(storageIdentifier);
	}

	public TeneoHibernateStorage() {
		preferenceStore = Activator.getDefault().getPreferenceStore();
		this.init(preferenceStore.getString(PreferenceConstants.STORAGE_DRIVER));
	}

	private void init(String storageIdentifier) {
		if (storageIdentifier == null) {
			this.storageIdentifier = "Default_TeneoHibernateStorage";
		} else {
			this.storageIdentifier = storageIdentifier;
		}
		this.loadStore();
		this.loadDefaultProperties();
	}

	public boolean isConnected() {
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager
					.getConnection(
							preferenceStore
									.getString(PreferenceConstants.STORAGE_URI),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_USER),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_PW));

		} catch (ClassNotFoundException e) {
			System.out.println(e.getMessage());
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return con != null;
	}

	private void loadStore() {
		this.dataStore = HbHelper.INSTANCE
				.createRegisterDataStore(this.storageIdentifier);
		this.dataStore.setEPackages(new EPackage[] { ModelPackage.eINSTANCE });
	}

	@SuppressWarnings("restriction")
	private void loadDefaultProperties() {
		Properties props = new Properties();
		String driver = preferenceStore
				.getString(PreferenceConstants.STORAGE_DRIVER);
		String user = preferenceStore
				.getString(PreferenceConstants.STORAGE_USER);
		String url = preferenceStore.getString(PreferenceConstants.STORAGE_URI);
		String db = preferenceStore.getString(PreferenceConstants.STORAGE_DB);
		String password = preferenceStore
				.getString(PreferenceConstants.STORAGE_PW);
		String dialect = MySQL5Dialect.class.getName();

		if (!(driver.isEmpty() || user.isEmpty() || url.isEmpty()
				|| db.isEmpty() || password.isEmpty())) {
			props.setProperty(Environment.DRIVER, driver);
			props.setProperty(Environment.USER, user);
			props.setProperty(Environment.URL, url + "/" + db);
			props.setProperty(Environment.PASS, password);
			
		} else {
			props.setProperty(Environment.URL,
					"jdbc:mysql://127.0.0.1:3306/sonar_db");
			props.setProperty(Environment.DRIVER,"com.mysql.jdbc.Driver");
			props.setProperty(Environment.USER, "root");
			props.setProperty(Environment.PASS, "password");
		}
		props.setProperty(Environment.DIALECT, dialect);
		props.setProperty(PersistenceOptions.CASCADE_POLICY_ON_CONTAINMENT,
				"ALL");
		props.setProperty(PersistenceOptions.CASCADE_POLICY_ON_NON_CONTAINMENT,
				"MERGE,PERSIST,REFRESH");
		props.setProperty("teneo.mapping.always_version", "true");
		this.dataStore.setDataStoreProperties(props);
	}

	public void initDataStore() {
		if (!this.dataStore.isInitialized()) {
			this.dataStore.initialize();
		}
	}

	public void closeDataStore() {
		if (this.dataStore.isInitialized()) {
			this.dataStore.close();
		}
	}

	public boolean isInitialized() {
		return this.dataStore.isInitialized();
	}

	public void saveEntity(EObject object) {
		SessionFactory factory = this.dataStore.getSessionFactory();
		Session session = factory.openSession();
		session.beginTransaction();
		session.save(object);
		session.getTransaction().commit();
		session.close();
	}

	public void saveOrUpdateEntity(Collection<? extends EObject> list) {

		SessionFactory factory = this.dataStore.getSessionFactory();
		Session session = factory.openSession();

		session.beginTransaction();
		for (EObject object : list) {
			session.saveOrUpdate(object);
		}
		session.getTransaction().commit();
		session.close();
	}

	public void saveOrUpdateEntity(EObject object) {
		SessionFactory factory = this.dataStore.getSessionFactory();
		Session session = factory.openSession();

		session.beginTransaction();
		session.saveOrUpdate(object);
		session.getTransaction().commit();
	}

	public void deleteEntity(EObject object) {
		SessionFactory factory = this.dataStore.getSessionFactory();
		Session session = factory.openSession();
		session.beginTransaction();
		session.delete(object);
		session.getTransaction().commit();
		session.close();
	}

	public StorageResult executeListQuery(String queryString) {
		SessionFactory factory = this.dataStore.getSessionFactory();
		Session session = factory.openSession();
		session.beginTransaction();
		Query query = session.createQuery(queryString);
		StorageResult result = new StorageResult(session,
				(List<?>) query.list());
		return result;
	}

	public StorageResult executeUniqueQuery(String queryString) {
		SessionFactory factory = this.dataStore.getSessionFactory();
		Session session = factory.openSession();
		session.beginTransaction();
		Query query = session.createQuery(queryString);
		StorageResult result = new StorageResult(session, query.uniqueResult());
		return result;
	}

	public StorageResult executeListSQLQuery(String queryString) {
		Session session = this.dataStore.getSessionFactory().openSession();
		session.beginTransaction();
		SQLQuery query = session.createSQLQuery(queryString);
		StorageResult result = new StorageResult(session,
				(List<?>) query.list());
		return result;
	}

	public StorageResult executeUniqueSQLQuery(String queryString) {
		Session session = this.dataStore.getSessionFactory().openSession();
		session.beginTransaction();
		SQLQuery query = session.createSQLQuery(queryString);
		StorageResult result = new StorageResult(session, query.uniqueResult());
		return result;
	}

	public void createNewModelDB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager
					.getConnection(
							preferenceStore
									.getString(PreferenceConstants.STORAGE_URI),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_USER),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_PW));
			DatabaseMetaData meta = con.getMetaData();
			ResultSet rs = meta.getCatalogs();
			boolean dropDbFirst = false;
			while (rs.next()) {
				if (rs.getString("TABLE_CAT").equals(
						preferenceStore
								.getString(PreferenceConstants.STORAGE_DB))) {
					dropDbFirst = true;
					break;
				}
			}
			Statement statement;
			if (dropDbFirst) {
				statement = con.createStatement();
				statement.execute("DROP DATABASE "
						+ preferenceStore
								.getString(PreferenceConstants.STORAGE_DB));
			}
			statement = con.createStatement();
			statement
					.execute("CREATE DATABASE "
							+ preferenceStore
									.getString(PreferenceConstants.STORAGE_DB));
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public String getDatabaseName() {

		/*
		 * FIXME: Why preference store is null, which is different from the one
		 * in method 'createNewDB()'???
		 */

		String dbName = preferenceStore
				.getString(PreferenceConstants.STORAGE_DB);
		try {
			Class.forName("com.mysql.jdbc.Driver");
			com.mysql.jdbc.Driver driver;
			Connection con = DriverManager
					.getConnection(
							preferenceStore
									.getString(PreferenceConstants.STORAGE_URI),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_USER),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_PW));
			DatabaseMetaData meta = con.getMetaData();
			ResultSet rs = meta.getCatalogs();
			while (rs.next()) {
				if (rs.getString("TABLE_CAT").equals(
						preferenceStore
								.getString(PreferenceConstants.STORAGE_DB))) {
					return dbName;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return "";
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return "";
		}
		return "";
	}

	public boolean hasDatabase() {

		boolean result = false;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager
					.getConnection(
							preferenceStore
									.getString(PreferenceConstants.STORAGE_URI),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_USER),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_PW));
			DatabaseMetaData meta = con.getMetaData();
			ResultSet rs = meta.getCatalogs();
			while (rs.next()) {
				if (rs.getString("TABLE_CAT").equals(
						preferenceStore
								.getString(PreferenceConstants.STORAGE_DB))) {
					return result;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return result;
	}

	public void dropDatabase() {

		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager
					.getConnection(
							preferenceStore
									.getString(PreferenceConstants.STORAGE_URI),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_USER),
							preferenceStore
									.getString(PreferenceConstants.STORAGE_PW));
			DatabaseMetaData meta = con.getMetaData();
			ResultSet rs = meta.getCatalogs();
			Statement statement = con.createStatement();
			boolean succeed = statement
					.execute("DROP DATABASE "
							+ preferenceStore
									.getString(PreferenceConstants.STORAGE_DB));

			MessageDialog.open(MessageDialog.INFORMATION, PlatformUI
					.getWorkbench().getDisplay().getActiveShell(),
					"Drop database",
					"You succeed to drop the selected database", SWT.NONE);

		} catch (SQLException e) {
			e.printStackTrace();
			MessageDialog.open(MessageDialog.INFORMATION, PlatformUI
					.getWorkbench().getDisplay().getActiveShell(),
					"Drop database",
					"You fail to drop the selected database. The message is as follows: "
							+ e.getMessage(), SWT.NONE);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			MessageDialog.open(MessageDialog.INFORMATION, PlatformUI
					.getWorkbench().getDisplay().getActiveShell(),
					"Class Not Found", e.getMessage(), SWT.NONE);
		}
	}

}
